At 21:00 +0200 on 01/11/1999, amy cheng wrote:
> then, why use int, isn't char better? (almost no need for batch).
It all depends in the application. First, one must note that a
variable-length char takes more space (More than four bytes), and also,
variable-length fields have a slight performance penalty in retrieval.
Second, the question is what you want to do with it. I once had an
application in which the data items were lines in a piece of text. One
could add a line anywhere. So, if you wanted to insert a line between line
number 340 and line number 420, it was easy to find a number in between -
the average of the two numbers (420+340)/2 = 380. This would give you some
space for later addition between line 340 and 380 - using the same formula
- or between 380 and 420.
I think doing such a calculation in text is a bit more awkward. But it all
depends on whether you need to allow inserts on a regular basis, and assign
the orders manually. It's a question of design.
BTW, I think my suggestion for batch renumbering won't work in all cases. I
think a better alternative would be:
SELECT the_order as old_order, 0 as new_order
INTO TABLE temp_numbers
FROM questions
ORDER BY old_order;
CREATE SEQUENCE new_seq INCREMENT 100 START 100;
UPDATE temp_numbers
SET new_order = nextval( 'new_seq' );
UPDATE questions
SET the_order = temp_numbers.new_order
WHERE questions.the_order = temp_numbers.old_order;
DROP SEQUENCE new_seq;
DROP TABLE temp_numbers;
I also think this can be done more easily in a pl/pgsql function, but I am
not exactly an expert on them.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma